<?php 
if (!defined('BASEPATH')){
	exit('No direct script access allowed');
}

/**
 *  Listcustomers_model Model
 *
 */

class Listcustomers_model extends MY_Model{

	public function __construct(){
		parent::__construct();
		$this->table_name = 'book_contacts';
	}
        
        public function selectBS($params){
                $this->db->select('name,phone,salesID,totalMoneyBooking,owner')
                ->from('book_contacts AS c')
                ->where('salesID > 0')
                ->where('lastupdated >= ',$params['begin'])
                ->where('lastupdated <= ',$params['end']);
                $query = $this->db->get();
//                echo $this->db->last_query();exit;
                return $query->result();
        }

        public function searchCustomers($name=NULL,$phone=NULL){
            
            $this->db->select('c.customerID,c.name,c.address,c.phone,c.social,DATE_FORMAT(c.birthday,("%d-%m-%Y")) AS birthday,c.info_bill,c.note,c.lastupdated,c.lastBooking,c.totalMoneyBooking,c.surplus,c.salesID,t.name AS type,ag.pcc AS agent_code,prov.name AS province,usr.username AS username')
                ->from('book_contacts AS c')
                ->join('book_type_customers AS t','t.id = c.type')
                ->join('book_agent_code AS ag','c.agent_code = ag.id')
                ->join('book_province AS prov','prov.id = c.province')
                ->join('users AS usr','usr.id = c.owner');
            
            if($name){
                 $this->db->where('c.name REGEXP ("'.$name.'")');
            }
            if($phone){
                $this->db->like('c.phone',$phone);
            }
            $query = $this->db->get();
//            echo $this->db->last_query();exit;
            return $query->result();
        }

        public function getListUsersDist($params){
            $getListUsers       = $this->getListUsers();
            $info               = $this->listcustomers_model->find_by(array('customerID'=>$params));
            $check              = json_decode($info[0]->share);

            foreach($check as $value){
                if (array_key_exists($value, $getListUsers)) {
                    unset($getListUsers[$value]);
                }
            }
            return $getListUsers;
        }
        
        public function updateMoneyTotal($params1,$params2,$params3){
            $sql = 'update '.$this->table_name.' dest ,(SELECT totalMoneyBooking,customerID FROM '.$this->table_name.' WHERE customerID = "'.$params1.'") src SET dest.customerID = src.customerID, dest.totalMoneyBooking = src.totalMoneyBooking + '.$params2.', dest.lastBooking = "'.$params3.'" WHERE dest.customerID = "'.$params1.'"';
            $this->db->query($sql);
            return $this->db->affected_rows();
        }
        
        public function updateSurplus($params1,$params2){
            $sql = 'update '.$this->table_name.' SET surplus = surplus + "'.$params2.'" WHERE customerID = "'.$params1.'"';
            $this->db->query($sql);
            return $this->db->affected_rows();
        }
		
		public function updateSurplusChung($params1,$params2){
            $sql = 'update '.$this->table_name.' SET surplus = "'.$params2.'" WHERE customerID = "'.$params1.'"';
            $this->db->query($sql);
            return $this->db->affected_rows();
        }
        
        public function updateSurplusTicket($params1,$params2){
            $sql = 'update '.$this->table_name.' SET surplus = surplus + "'.$params2.'" WHERE customerID = "'.$params1.'"';
            $this->db->query($sql);
            return $this->db->affected_rows();
        }

	public function init_data($right){
		return array(
				array(
						'name' => 'name',
						'label' => $this->lang->line('name'),
						'width' => '10%',
						'sort'  => 'ASC',
						'searchoptions' => FALSE
				),array(
						'name' 	=> 'address',
						'label' => $this->lang->line('address'),
						'width' => '20%',
						'sort'  => FALSE,
						'searchoptions' => FALSE
				),
				array(
						'name' 	=> 'phone',
						'label' => $this->lang->line('phone'),
						'width' => '10%',
						'sort'  => FALSE,
						'searchoptions' => FALSE
				),
                                array(
						'name' 	=> 'type_customers',
						'label' => $this->lang->line('type_customers'),
						'width' => '10%',
						'sort'  => FALSE,
						'searchoptions' => FALSE
				),array(
						'name' 	=> 'username',
						'label' => $this->lang->line('updatedby'),
						'width' => '10%',
						'sort'  => FALSE,
						'searchoptions' => FALSE
				),array(
						'name' 	=> 'agent_code',
						'label' => $this->lang->line('agent_code'),
						'width' => '10%',
						'sort'  => FALSE,
						'searchoptions' => FALSE
				),
				array(
						'name'  => 'button',
						'width' => '10%',
						'sort'  => FALSE,
						'label'  => $right['add']==TRUE?'<div class="btn-group">
						<a style="width: 82px;" href="'.site_url('auth/listcustomers/index/add').'" class="btn btn btn-success">'.$this->lang->line('create').'</a>
						</div>':'',
						'searchoptions' => FALSE
				)
		);
	}
	
	/*
	 * datatables data
	* return json
	*/
	public function json_data($controller, $right){
            
                if($this->session->userdata('s_listcustomers')){
                    $s_listcustomers = json_decode($this->session->userdata('s_listcustomers'));
                }
                
		$this->datatables
		->select('c.name,c.customerID,c.address,c.phone,c.lastupdated,c.active,t.name AS type,ag.pcc AS agent_code,prov.name AS province,usr.username AS username,usr.id AS user_id')
                ->from($this->table_name . ' AS c')
                ->join('book_type_customers AS t','t.id = c.type')
                ->join('book_agent_code AS ag','c.agent_code = ag.id')
                ->join('book_province AS prov','prov.id = c.province')
                ->join('users AS usr','usr.id = c.owner')
                ->where('c.active = 1')
                ->where('c.share LIKE ','%'.$this->session->userdata['user_id'] .'%');
                
                if($this->session->userdata['agent_code']){
                    $this->datatables->where('c.agent_code = '.$this->session->userdata['agent_code']);
                }
                
                if(isset($s_listcustomers) && $s_listcustomers->s_customerName != ''){
                    $this->datatables->where('c.name REGEXP ("'.$s_listcustomers->s_customerName.'")');
                }
                
                if(isset($s_listcustomers) && $s_listcustomers->s_phone != ''){
                    $this->datatables->like('c.phone',$s_listcustomers->s_phone,'both');
                }
                
		$this->datatables->set_produce_output(false);
		$ouput = $datatables = $this->datatables->generate();
		unset($ouput['aaData']);
		$ouput['aaData'] = array();
                
                $this->session->unset_userdata('s_listcustomers');

		foreach($datatables['aaData'] as $item){
			$ouput['aaData'][] = array(
					$item['name'],
                            		$item['address'],
					implode("<br/>", json_decode($item['phone'])),
                                        $item['type'],
                                        $item['username'],
                                        $item['agent_code'],
					$this->add_button_customers($controller, $right, $item)
			);
		}
	
		return json_encode($ouput);
	}
}